from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from flask_cors import CORS
from sqlalchemy import func, cast, Float

app = Flask(__name__)
# 开启跨域支持
CORS(app)

# --------------------
# 数据库配置：请修改为你的 MySQL 数据库连接信息
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:root@localhost:3306/car_sales'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)


# --------------------
# 数据库模型定义
# --------------------
class CarConfig(db.Model):
    __tablename__ = 'car_config'
    id = db.Column(db.BigInteger, primary_key=True)
    series_id = db.Column(db.BigInteger, nullable=True)
    series_name = db.Column(db.Text, nullable=True)
    battery_warranty = db.Column(db.Text, nullable=True)
    motor_type = db.Column(db.Text, nullable=True)
    max_speed_kmh = db.Column(db.Text, nullable=True)
    energy_consumption_kwh_per_100km = db.Column(db.Text, nullable=True)
    energy_type = db.Column(db.Text, nullable=True)
    total_motor_torque_nm = db.Column(db.Text, nullable=True)
    battery_type = db.Column(db.Text, nullable=True)
    drive_type = db.Column(db.Text, nullable=True)
    motor_layout = db.Column(db.Text, nullable=True)
    fast_charge_time_hours = db.Column(db.Text, nullable=True)
    total_motor_power_kw = db.Column(db.Text, nullable=True)
    battery_capacity_kwh = db.Column(db.Text, nullable=True)
    vehicle_type = db.Column(db.Text, nullable=True)
    charging_time_hours = db.Column(db.Text, nullable=True)
    range_km = db.Column(db.Text, nullable=True)
    acceleration_0_100_kmh = db.Column(db.Text, nullable=True)
    curb_weight_kg = db.Column(db.Text, nullable=True)
    motor_count = db.Column(db.Text, nullable=True)
    slow_charge_time_hours = db.Column(db.Text, nullable=True)


class CarScore(db.Model):
    __tablename__ = 'car_score'
    id = db.Column(db.BigInteger, primary_key=True)
    series_id = db.Column(db.BigInteger, nullable=True)
    series_name = db.Column(db.Text, nullable=True)
    overall_score = db.Column(db.Text, nullable=True)
    exterior_score = db.Column(db.Text, nullable=True)
    interior_score = db.Column(db.Text, nullable=True)
    handling_score = db.Column(db.Text, nullable=True)
    power_score = db.Column(db.Text, nullable=True)
    space_score = db.Column(db.Text, nullable=True)
    comfort_score = db.Column(db.Text, nullable=True)


class ElectricVehicleSales(db.Model):
    __tablename__ = 'electric_vehicle_sales'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    series_id = db.Column(db.Integer, nullable=False)
    series_name = db.Column(db.String(255), nullable=True)
    brand_id = db.Column(db.Integer, nullable=True)
    brand_name = db.Column(db.String(255), nullable=True)
    min_price = db.Column(db.Numeric(10, 2), nullable=True)
    max_price = db.Column(db.Numeric(10, 2), nullable=True)
    count = db.Column(db.Integer, nullable=True)
    image = db.Column(db.String(255), nullable=True)
    time_period = db.Column(db.String(64), nullable=False)




# 1. 排行榜接口：返回 CarScore 表中 overall_score 排名前10（按分数降序排序）
@app.route('/api/rankings', methods=['GET'])
def rankings():
    try:
        # 使用 cast 将字符串转成浮点数排序，注意确保数据库中数据格式可转换
        ranked_cars = CarScore.query.order_by(cast(CarScore.overall_score, Float).desc()).limit(10).all()
        result = []
        for car in ranked_cars:
            result.append({
                'series_id': car.series_id,
                'series_name': car.series_name,
                'overall_score': car.overall_score,
                'exterior_score': car.exterior_score,
                'interior_score': car.interior_score,
                'handling_score': car.handling_score,
                'power_score': car.power_score,
                'space_score': car.space_score,
                'comfort_score': car.comfort_score
            })
        return jsonify(result)
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 2. 分页查询 CarConfig 数据
@app.route('/api/car_configs', methods=['GET'])
def get_car_configs():
    try:
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 10, type=int)
        pagination = CarConfig.query.paginate(page=page, per_page=per_page, error_out=False)
        items = []
        for car in pagination.items:
            items.append({
                'id': car.id,
                'series_id': car.series_id,
                'series_name': car.series_name,
                'battery_warranty': car.battery_warranty,
                'motor_type': car.motor_type,
                'max_speed_kmh': car.max_speed_kmh,
                # 可根据需要添加其它字段
            })
        return jsonify({
            'total': pagination.total,
            'pages': pagination.pages,
            'page': page,
            'per_page': per_page,
            'items': items
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 3. 获取单个 CarConfig 记录详情
@app.route('/api/car_configs/<int:config_id>', methods=['GET'])
def get_car_config(config_id):
    try:
        car = CarConfig.query.get(config_id)
        if not car:
            return jsonify({'error': 'Car configuration not found'}), 404
        result = {
            'id': car.id,
            'series_id': car.series_id,
            'series_name': car.series_name,
            'battery_warranty': car.battery_warranty,
            'motor_type': car.motor_type,
            'max_speed_kmh': car.max_speed_kmh,
            'energy_consumption_kwh_per_100km': car.energy_consumption_kwh_per_100km,
            'energy_type': car.energy_type,
            'total_motor_torque_nm': car.total_motor_torque_nm,
            'battery_type': car.battery_type,
            'drive_type': car.drive_type,
            'motor_layout': car.motor_layout,
            'fast_charge_time_hours': car.fast_charge_time_hours,
            'total_motor_power_kw': car.total_motor_power_kw,
            'battery_capacity_kwh': car.battery_capacity_kwh,
            'vehicle_type': car.vehicle_type,
            'charging_time_hours': car.charging_time_hours,
            'range_km': car.range_km,
            'acceleration_0_100_kmh': car.acceleration_0_100_kmh,
            'curb_weight_kg': car.curb_weight_kg,
            'motor_count': car.motor_count,
            'slow_charge_time_hours': car.slow_charge_time_hours
        }
        return jsonify(result)
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 4. 分页查询 ElectricVehicleSales 数据
@app.route('/api/sales', methods=['GET'])
def get_sales():
    try:
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 10, type=int)
        pagination = ElectricVehicleSales.query.paginate(page=page, per_page=per_page, error_out=False)
        items = []
        for sale in pagination.items:
            items.append({
                'id': sale.id,
                'series_id': sale.series_id,
                'series_name': sale.series_name,
                'brand_id': sale.brand_id,
                'brand_name': sale.brand_name,
                'min_price': str(sale.min_price) if sale.min_price is not None else None,
                'max_price': str(sale.max_price) if sale.max_price is not None else None,
                'count': sale.count,
                'time_period': sale.time_period,
            })
        return jsonify({
            'total': pagination.total,
            'pages': pagination.pages,
            'page': page,
            'per_page': per_page,
            'items': items
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 5. 年份数据分析接口：统计 ElectricVehicleSales 表中各 time_period 的销售总量
@app.route('/api/sales/yearly_analysis', methods=['GET'])
def yearly_sales_analysis():
    try:
        results = db.session.query(
            ElectricVehicleSales.time_period,
            func.sum(ElectricVehicleSales.count).label('total_sales')
        ).group_by(ElectricVehicleSales.time_period).all()

        data = [{'year': r[0], 'total_sales': int(r[1] or 0)} for r in results]
        return jsonify(data)
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 6. 分页查询 CarScore 数据
@app.route('/api/car_scores', methods=['GET'])
def get_car_scores():
    try:
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 10, type=int)
        pagination = CarScore.query.paginate(page=page, per_page=per_page, error_out=False)
        items = []
        for score in pagination.items:
            items.append({
                'id': score.id,
                'series_id': score.series_id,
                'series_name': score.series_name,
                'overall_score': score.overall_score,
                'exterior_score': score.exterior_score,
                'interior_score': score.interior_score,
                'handling_score': score.handling_score,
                'power_score': score.power_score,
                'space_score': score.space_score,
                'comfort_score': score.comfort_score
            })
        return jsonify({
            'total': pagination.total,
            'pages': pagination.pages,
            'page': page,
            'per_page': per_page,
            'items': items
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 7. 获取单个 CarScore 记录详情
@app.route('/api/car_scores/<int:score_id>', methods=['GET'])
def get_car_score(score_id):
    try:
        score = CarScore.query.get(score_id)
        if not score:
            return jsonify({'error': 'Car score record not found'}), 404
        result = {
            'id': score.id,
            'series_id': score.series_id,
            'series_name': score.series_name,
            'overall_score': score.overall_score,
            'exterior_score': score.exterior_score,
            'interior_score': score.interior_score,
            'handling_score': score.handling_score,
            'power_score': score.power_score,
            'space_score': score.space_score,
            'comfort_score': score.comfort_score
        }
        return jsonify(result)
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 8. 搜索 CarConfig，根据 series_name 模糊匹配
@app.route('/api/car_configs/search', methods=['GET'])
def search_car_configs():
    try:
        keyword = request.args.get('keyword', '', type=str)
        if not keyword:
            return jsonify({'error': 'Please provide a search keyword'}), 400
        results = CarConfig.query.filter(CarConfig.series_name.like(f"%{keyword}%")).all()
        data = []
        for car in results:
            data.append({
                'id': car.id,
                'series_id': car.series_id,
                'series_name': car.series_name,
                'motor_type': car.motor_type,
                'max_speed_kmh': car.max_speed_kmh
            })
        return jsonify(data)
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 9. 搜索 ElectricVehicleSales，根据 brand_name 或 series_name 模糊匹配
@app.route('/api/sales/search', methods=['GET'])
def search_sales():
    try:
        keyword = request.args.get('keyword', '', type=str)
        if not keyword:
            return jsonify({'error': 'Please provide a search keyword'}), 400
        results = ElectricVehicleSales.query.filter(
            (ElectricVehicleSales.brand_name.like(f"%{keyword}%")) |
            (ElectricVehicleSales.series_name.like(f"%{keyword}%"))
        ).all()
        data = []
        for sale in results:
            data.append({
                'id': sale.id,
                'series_id': sale.series_id,
                'series_name': sale.series_name,
                'brand_name': sale.brand_name,
                'min_price': str(sale.min_price) if sale.min_price is not None else None,
                'max_price': str(sale.max_price) if sale.max_price is not None else None,
                'count': sale.count,
                'time_period': sale.time_period,
                'image': sale.image,
            })
        return jsonify(data)
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 10. 统计接口：计算 ElectricVehicleSales 各 time_period 的平均价格（取 min_price 与 max_price 的平均值）
@app.route('/api/stats/average_price', methods=['GET'])
def average_price_stats():
    try:
        # 假设每个销售记录的平均价格 = (min_price + max_price) / 2 ，然后对每个 time_period 计算总均价
        subquery = db.session.query(
            ElectricVehicleSales.time_period,
            ((ElectricVehicleSales.min_price + ElectricVehicleSales.max_price) / 2).label('avg_price')
        ).subquery()

        results = db.session.query(
            subquery.c.time_period,
            func.avg(subquery.c.avg_price).label('average_price')
        ).group_by(subquery.c.time_period).all()

        data = []
        for r in results:
            data.append({
                'time_period': r[0],
                'average_price': float(r[1]) if r[1] is not None else None
            })
        return jsonify(data)
    except Exception as e:
        return jsonify({'error': str(e)}), 500



if __name__ == '__main__':
    # 首次运行时可以取消下面两行注释以创建数据库和表
    # with app.app_context():
    #     db.create_all()

    app.run(host='0.0.0.0', port=5000, debug=True)
